
import asyncio
import oracledb
from typing import Dict, Tuple, List, Any

# 全局连接池
pool = None


async def init_pool():
    """初始化 Oracle 连接池（异步方式）"""
    global pool
    pool = await oracledb.create_pool_async(
        user="your_username",
        password="your_password",
        dsn="localhost:1521/ORCLCDB",
        min=2,
        max=10,
        threaded=True  # 启用线程模式（兼容 asyncio）
    )


async def execute_query(conn, sql: str, params: Tuple = None) -> List[Tuple]:
    """执行单条查询并返回结果"""
    async with conn.cursor() as cursor:
        await cursor.execute(sql, params or ())
        return await cursor.fetchall()


async def batch_query(queries: Dict[str, Tuple[str, Tuple]]) -> Dict[str, List[Tuple]]:
    """
    批量执行 SQL 查询（字典格式）
    :param queries: { "key": ("SQL", params) } 或 { "key": "SQL" }
    :return: { "key": results }
    """
    results = {}
    async with pool.acquire() as conn:
        tasks = []
        for key, query in queries.items():
            sql, params = (query, ()) if isinstance(query, str) else query
            task = execute_query(conn, sql, params)
            tasks.append((key, task))

        # 并行执行所有查询
        for key, task in tasks:
            results[key] = await task
    return results


async def main():
    # 初始化连接池
    await init_pool()

    # 定义批量查询
    queries = {
        "high_salary": ("SELECT * FROM employees WHERE salary > :1", (5000,)),
        "dept_count": "SELECT COUNT(*) FROM departments",
        "avg_salary": "SELECT AVG(salary) FROM employees"
    }

    # 执行并打印结果
    results = await batch_query(queries)
    for key, data in results.items():
        print(f"{key} 结果:", data)

    # 关闭连接池
    await pool.close()


if __name__ == "__main__":
    asyncio.run(main())